﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for SQLQuery
/// </summary>
public class SQLQuery
{
    private EncryptMD5 encryptMD5 = new EncryptMD5();

    private DataTable datatable = null;
    private DataSet dataset = null;

    private SqlConnection myConnection = null;
    private SqlCommand myCommand = null;

    private SqlDataAdapter dataAdapter = null;
    private SqlDataReader dataReader = null;

    private string connectionText = ConfigurationManager.ConnectionStrings["connection"].ToString();

    private void openDB()
    {
        closeDB();
        if (myConnection != null)
        {
            myCommand = null;
            myConnection = new SqlConnection(connectionText);
        }
    }

    private void closeDB()
    {
        if (myConnection != null)
        {
            myConnection.Close();
            myConnection = null;
        }
    }

    public void loadDataGridView(GridView gridview, string query)
    {
        dataset = new DataSet();
        myConnection = new SqlConnection(connectionText);
        myCommand = new SqlCommand(query, myConnection);
        dataAdapter = new SqlDataAdapter(myCommand);
        dataAdapter.Fill(dataset);
        gridview.DataSource = dataset.Tables[0];
        gridview.DataBind();
    }

    public void fillDataToDataList(DataList datalist, string value, string table)
    {
        string query = "SELECT * FROM " + table + " WHERE [type]='" + value + "'";
        dataset = new DataSet();
        myConnection = new SqlConnection(connectionText);
        myCommand = new SqlCommand(query, myConnection);
        dataAdapter = new SqlDataAdapter(myCommand);
        dataAdapter.Fill(dataset);
        datalist.DataSource = dataset.Tables[0];
        datalist.DataBind();
    }

    public List<string> fillAllQuestion(string examCode)
    {
        List<string> list = new List<string>();
        try
        {
            string query = "SELECT * FROM Exam WHERE examCode='" + examCode + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            while (dataReader.Read())
            {
                list.Add(dataReader["quesCode"].ToString().Trim());
            }
            return list;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return null;
    }

    public void fillExam(GridView gridview, List<string> list)
    {
        datatable = new DataTable();
        datatable.Columns.Add("Number", typeof(string));
        datatable.Columns.Add("QuesCode", typeof(string));
        datatable.Columns.Add("Content", typeof(string));
        datatable.Columns.Add("Answer", typeof(string));
        try
        {
            int number = 0;
            for (int i = 0; i < list.Count; i++)
            {
                string query = "SELECT [content],[answer] FROM Questions WHERE [quesCode]='" + list[i] + "'";
                myConnection = new SqlConnection(connectionText);
                myCommand = new SqlCommand(query, myConnection);
                myConnection.Open();
                dataReader = myCommand.ExecuteReader();
                dataReader.Read();
                if (dataReader.HasRows)
                {
                    number++;
                    datatable.Rows.Add(Convert.ToString(number), list[i], dataReader["content"].ToString().Trim(), dataReader["answer"].ToString().Trim());
                }
            }

            gridview.DataSource = datatable;
            gridview.DataBind();
        }
        catch (Exception) { }
        finally { closeDB(); }
    }

    public List<string> selectAllQuestion(string value)
    {
        List<string> list = new List<string>();
        try
        {
            string query = "SELECT * FROM Questions WHERE [type]='" + value + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            while (dataReader.Read())
            {
                list.Add(dataReader["quesCode"].ToString().Trim());
            }
            return list;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return null;
    }

    public bool fillDataToListQuestion(DataList datalist, string value, string table)
    {
        try
        {
            string query = "SELECT * FROM " + table + " WHERE [type]='" + value + "'";
            dataset = new DataSet();
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            dataAdapter = new SqlDataAdapter(myCommand);
            dataAdapter.Fill(dataset);
            if (dataset.Tables[0].Rows.Count > 0)
            {
                datalist.DataSource = dataset.Tables[0];
                datalist.DataBind();
                return true;
            }
            else
            {
                dataset.Clear();
                datalist.DataSource = dataset.Tables[0];
                datalist.DataBind();                
            }           
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool selectEmailIsExits(string email, string table)
    {
        try
        {
            string quert = "SELECT * FROM " + table + " WHERE [email]='" + email + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(quert, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                return true;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool selectUserIsExits(string user, string table)
    {
        try
        {
            string quert = "SELECT * FROM " + table + " WHERE [user]='" + user + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(quert, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                return true;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool searchInforTableMamager(GridView mGridView, string value)
    {
        try
        {
            string query = "SELECT * FROM Manager WHERE [user] like '%" + value + "%' OR [name] like '%" + value + "%' OR [email] like '%" + value + "%' OR [phone] like '%" + value + "%' OR [permission] like '%" + value + "%'";
            dataset = new DataSet();
            dataAdapter = new SqlDataAdapter(query, connectionText);
            dataAdapter.Fill(dataset);
            if (dataset.Tables[0].Rows.Count > 0)
            {
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
            }
            else
            {
                dataset.Clear();
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
                return true;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool searchInforExamTest(GridView mGridView, string email)
    {
        try
        {
            string query = "SELECT * FROM Tests WHERE [email]='" + email.Trim() + "' ORDER BY [round]";
            dataset = new DataSet();
            dataAdapter = new SqlDataAdapter(query, connectionText);
            dataAdapter.Fill(dataset);
            if (dataset.Tables[0].Rows.Count > 0)
            {
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
            }
            else
            {
                dataset.Clear();
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
                return false;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return true;
    }

    public bool searchTestResults(GridView mGridView, string email)
    {
        try
        {
            string query = "SELECT * FROM Tests WHERE [email]='" + email.Trim() + "' AND [status]='Pass' OR [status]='Fails' ORDER BY [round]";
            dataset = new DataSet();
            dataAdapter = new SqlDataAdapter(query, connectionText);
            dataAdapter.Fill(dataset);
            if (dataset.Tables[0].Rows.Count > 0)
            {
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
            }
            else
            {
                dataset.Clear();
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
                return false;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return true;
    }

    public bool insertTableManager(string user, string pass, string name, string email, string phone, string permission, string avatar)
    {
        if (LocalVariables.isNullorEmpty(avatar))
        {
            avatar = "";
        }
        try
        {
            string query = "INSERT INTO Manager VALUES('" + user + "','" + encryptMD5.encrypt(pass) + "','" + name + "','" + email + "'," + phone + ",'" + permission + "','" + avatar + "')";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) {}
        finally { closeDB(); }
        return false;
    }

    public bool updateTableManager(string user, string pass, string name, string email, string phone, string permission, string avatar)
    {
        try
        {
            string query = "";
            if (pass.Length > 0 && !LocalVariables.isNullorEmpty(avatar))
            {
                query = "UPDATE Manager SET [pass]='" + encryptMD5.encrypt(pass) + "', [name]='" + name + "', [email]='" + email + "', [phone]=" + phone + ", [permission]='" + permission + "', [avatar]='" + avatar + "' WHERE [user]='" + user + "'";
            }
            else if (pass.Length > 0 && LocalVariables.isNullorEmpty(avatar))
            {
                query = "UPDATE Manager SET [pass]='" + encryptMD5.encrypt(pass) + "', [name]='" + name + "', [email]='" + email + "', [phone]=" + phone + ", [permission]='" + permission + "' WHERE [user]='" + user + "'";
            }
            else if (LocalVariables.isNullorEmpty(pass) && !LocalVariables.isNullorEmpty(avatar))
            {
                query = "UPDATE Manager SET [name]='" + name + "', [email]='" + email + "', [phone]=" + phone + ", [permission]='" + permission + "', [avatar]='" + avatar + "' WHERE [user]='" + user + "'";
            }
            else
            {
                query = "UPDATE Manager SET [name]='" + name + "', [email]='" + email + "', [phone]=" + phone + ", [permission]='" + permission + "' WHERE [user]='" + user + "'";
            }
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool deleteTable(string table, string column, string value)
    {
        try
        {
            string query = "DELETE " + table + " WHERE " + column + "='" + value + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    //-------------------------------------------------------------------------------------
    public bool selectQuesCodeIsExits(string table, string quesCode)
    {
        try
        {
            string quert = "SELECT * FROM " + table + " WHERE [quesCode]='" + quesCode + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(quert, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                return true;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool insertTableQuestions(string type,string quesCode, string content, string answer)
    {
        try
        {
            string query = "INSERT INTO Questions VALUES('" + type + "','" + quesCode + "','" + content + "','" + answer + "')";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool updateTableQuestions(string type, string quesCode, string content, string answer)
    {
        try
        {
            string query = "UPDATE Questions SET [type]='" + type + "', [content]='" + content + "', [answer]='" + answer + "' WHERE [quesCode]='" + quesCode + "'";
            
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool searchInforTableQuestions(GridView mGridView, string value)
    {
        try
        {
            string query = "SELECT * FROM Questions WHERE [type] like '%" + value + "%' OR [quesCode] like '%" + value + "%' OR [content] like '%" + value + "%' OR [answer] like '%" + value + "%' OR [score] like '%" + value + "%'";
            dataset = new DataSet();
            dataAdapter = new SqlDataAdapter(query, connectionText);
            dataAdapter.Fill(dataset);
            if (dataset.Tables[0].Rows.Count > 0)
            {
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
            }
            else
            {
                dataset.Clear();
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
                return true;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public string selectContentTableQuestions(string value)
    {
        try
        {
            string quert = "SELECT [content] FROM Questions WHERE [quesCode]='" + value + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(quert, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                return dataReader["content"].ToString().Trim();
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return "";
    }

    //-----------------------------------------------------
    public bool insertTableExam(string table, string examCode, string quesCode)
    {
        try
        {
            string query = "INSERT INTO " + table + " VALUES('" + examCode + "','" + quesCode + "')";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool selectExamQuesIsExits(string examCode, string quesCode)
    {
        try
        {
            string quert = "SELECT * FROM Exam WHERE [examCode]='" + examCode + "' AND [quesCode]='" + quesCode + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(quert, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                return true;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool selectExamIsExits(string examCode)
    {
        try
        {
            string quert = "SELECT * FROM Exam WHERE [examCode]='" + examCode + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(quert, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                return true;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public void deleteTableExam(string examCode, string quesCode)
    {
        try
        {
            string query = "DELETE Exam WHERE [examCode]='" + examCode + "' AND [quesCode]='" + quesCode + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
        }
        catch (Exception) { }
        finally { closeDB(); }
    }

    //---------------------------------------------------------
    public bool insertTableCandidate(string email, string pass, string name, string birthday,
                string address, string city, string country, string description, string phone, string avatar)
    {
        string datecreate = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss:mmm");
        if (LocalVariables.isNullorEmpty(avatar))
        {
            avatar = "";
        }
        try
        {
            string query = "INSERT INTO Candidate VALUES('" + email + "','" + encryptMD5.encrypt(pass) + "','" + name + "','" + birthday + "','" + address + "','" + city + "','" + country + "','" + description + "','" + phone + "','" + avatar + "','" + datecreate + "')";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool updateTableCandidate(string email, string pass, string name, string birthday,
                string address, string city, string country, string description, string phone, string avatar)
    {
        try
        {
            string query = "";
            if (pass.Length > 0 && !LocalVariables.isNullorEmpty(avatar))
            {
                query = "UPDATE Candidate SET [pass]='" + encryptMD5.encrypt(pass) + "', [name]='" + name + "', [birthday]='" + birthday + "', [address]='" + address + "', [city]='" + city + "', [country]='" + country + "', [description]='" + description + "', [phone]='" + phone + "', [avatar]='" + avatar + "' WHERE [email]='" + email + "'";
            }
            else if (pass.Length > 0 && LocalVariables.isNullorEmpty(avatar))
            {
                query = "UPDATE Candidate SET [pass]='" + encryptMD5.encrypt(pass) + "', [name]='" + name + "', [birthday]='" + birthday + "', [address]='" + address + "', [city]='" + city + "', [country]='" + country + "', [description]='" + description + "', [phone]='" + phone + "' WHERE [email]='" + email + "'";
            }
            else if (LocalVariables.isNullorEmpty(pass) && !LocalVariables.isNullorEmpty(avatar))
            {
                query = "UPDATE Candidate SET [name]='" + name + "', [birthday]='" + birthday + "', [address]='" + address + "', [city]='" + city + "', [country]='" + country + "', [description]='" + description + "', [phone]='" + phone + "', [avatar]='" + avatar + "' WHERE [email]='" + email + "'";
            }
            else
            {
                query = "UPDATE Candidate SET [name]='" + name + "', [birthday]='" + birthday + "', [address]='" + address + "', [city]='" + city + "', [country]='" + country + "', [description]='" + description + "', [phone]='" + phone + "'  WHERE [email]='" + email + "'";
            }
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public string[] selectInforItemCandidate(string value)
    {
        string[] ARRAY = new string[8];
        string query = "SELECT * FROM Candidate WHERE [email]='" + value + "'";
        myConnection = new SqlConnection(connectionText);
        myCommand = new SqlCommand(query, myConnection);
        myConnection.Open();
        dataReader = myCommand.ExecuteReader();
        dataReader.Read();

        if (dataReader.HasRows)
        {
            ARRAY[0] = dataReader["name"].ToString().Trim();
            ARRAY[1] = dataReader["address"].ToString().Trim();
            ARRAY[2] = dataReader["city"].ToString().Trim();
            ARRAY[3] = dataReader["country"].ToString().Trim();
            ARRAY[4] = dataReader["phone"].ToString().Trim();
            ARRAY[5] = dataReader["description"].ToString().Trim();
            ARRAY[6] = dataReader["birthday"].ToString().Trim();
            ARRAY[7] = dataReader["avatar"].ToString().Trim();
            return ARRAY;
        }
        return null;
    }

    public bool searchInforTableCandidate(GridView mGridView, string value)
    {
        try
        {
            string query = "SELECT * FROM Candidate WHERE [city] like '%" + value + "%' OR [country] like '%" + value + "%' OR [address] like '%" + value + "%' OR [name] like '%" + value + "%' OR [email] like '%" + value + "%' OR [phone] like '%" + value + "%' OR [birthday] like '%" + value + "%'";
            dataset = new DataSet();
            dataAdapter = new SqlDataAdapter(query, connectionText);
            dataAdapter.Fill(dataset);
            if (dataset.Tables[0].Rows.Count > 0)
            {
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
            }
            else
            {
                dataset.Clear();
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
                return true;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    //------------------------------------------------------
    public void loadExamListData(DropDownList ddlist)
    {
        try
        {
            string query = "SELECT examCode from ExamCode";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            while (dataReader.Read())
            {
                ddlist.Items.Add(dataReader["examCode"].ToString());
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
    }

    public int getRoundCount(string value)
    {
        int count = 0;        
        try
        {
            string query = "SELECT COUNT(*) FROM Tests WHERE [email]='" + value + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            dataReader.Read();
            if (dataReader.HasRows)
            {
                count = Convert.ToInt32(dataReader[0].ToString());
                count++;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return count;
    }

    public bool insertTableTest(string email, string codelogin, string pass, string round, string examcode, string datetest, string timestart)
    {        
        try
        {
            string query = "INSERT INTO Tests(email,codelogin,pass,[round],datetest,timestart,examtest,[status])";
            query += "VALUES('" + email + "','" + codelogin.ToUpper() + "','" + pass + "','" + round + "','" + datetest + "','" + timestart + "','" + examcode + "','waiting')";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool updateTableTest(string email, string codelogin, string pass, string round, string examtest, string datetest, string timestart)
    {
        try
        {
            string query = "UPDATE Tests SET [codelogin]='" + codelogin + "', [pass]='" + pass + "', [examtest]='" + examtest + "', [datetest]='" + datetest + "', [timestart]='" + timestart + "' WHERE [email]='" + email + "' AND [round]='" + round + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public void deleteTableTest(string email, string round)
    {
        try
        {
            string query = "DELETE Tests WHERE [email]='" + email + "' AND [round]='" + round + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            myCommand.ExecuteNonQuery();
        }
        catch (Exception) { }
        finally { closeDB(); }
    }

    public string[] selectInforItemTests(string email, string round)
    {
        try
        {
            string[] ARRAY = new string[10];
            string query = "SELECT * FROM Tests WHERE [email]='" + email.Trim() + "' AND [round]='" + round + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                ARRAY[0] = dataReader["codelogin"].ToString().Trim();
                ARRAY[1] = dataReader["pass"].ToString().Trim();
                ARRAY[2] = dataReader["round"].ToString().Trim();
                ARRAY[3] = dataReader["datetest"].ToString().Trim();
                ARRAY[4] = dataReader["timestart"].ToString().Trim();
                ARRAY[5] = dataReader["examtest"].ToString().Trim();
                ARRAY[6] = dataReader["answertest"].ToString().Trim();
                ARRAY[7] = dataReader["results"].ToString().Trim();
                ARRAY[8] = dataReader["timecompleted"].ToString().Trim();
                ARRAY[9] = dataReader["status"].ToString().Trim();
                return ARRAY;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }        
        return null;
    }

    public bool searchInforTableTest(GridView mGridView, string value)
    {
        try
        {
            string query = "SELECT * FROM Tests WHERE [email] like '%" + value + "%' OR [codelogin] like '%" + value + "%' OR [round] like '%" + value + "%' OR [datetest] like '%" + value + "%' OR [timestart] like '%" + value + "%' OR [examtest] like '%" + value + "%' OR [answertest] like '%" + value + "%' OR [results] like '%" + value + "%' OR [timecompleted] like '%" + value + "%' OR [status] like '%" + value + "%'";
            dataset = new DataSet();
            dataAdapter = new SqlDataAdapter(query, connectionText);
            dataAdapter.Fill(dataset);
            if (dataset.Tables[0].Rows.Count > 0)
            {
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
            }
            else
            {
                dataset.Clear();
                mGridView.DataSource = dataset.Tables[0];
                mGridView.DataBind();
                return true;
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool updateChangePassword(string email, string newpassword)
    {
        try
        {
            string query = "UPDATE Candidate SET [pass]='" + newpassword + "' WHERE [email]='" + email + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    private string[] loadQuestionExam(string examCode)
    {
        string[] array = new string[10];
        try
        {
            string query = "SELECT [quesCode] FROM Exam WHERE [examCode]='" + examCode + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            int index = 0;
            while (dataReader.Read())
            {
                array[index] = dataReader["quesCode"].ToString().Trim();
                index++;
            }
            return array;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return null;
    }

    public void loadContentQuestion(string examCode)
    {
        try
        {
            string[] quesCode = loadQuestionExam(examCode);
            for (int i = 0; i < quesCode.Length; i++)
            {

                string query = "SELECT [content],[answer] FROM Questions WHERE [quesCode]='" + quesCode[i] + "'";
                myConnection = new SqlConnection(connectionText);
                myCommand = new SqlCommand(query, myConnection);
                myConnection.Open();
                dataReader = myCommand.ExecuteReader();
                dataReader.Read();
                if(dataReader.HasRows)
                {
                    LocalVariables.CONTENT[i] = dataReader["content"].ToString().Trim();
                    LocalVariables.ANSWER_TRUE[i] = dataReader["answer"].ToString().Trim();
                }
                closeDB();
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
    }

    public bool updateTested(string email, string round, int type, string answertest, string results, string status)
    {
        try
        {
            string query = "";
            if (type == 0)
            {
                query = "UPDATE Tests SET [status]='tested' WHERE [email]='" + email + "' AND [round]='" + round + "'";
            }
            else
            {
                query = "UPDATE Tests SET [answertest]='" + answertest + "', [results]='" + results + "', [timecompleted]='" + LocalVariables.getCurrentDateTime() + "', [status]='" + status + "' WHERE [email]='" + email + "' AND [round]='" + round + "'";
            }
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);

            myConnection.Open();
            myCommand.ExecuteNonQuery();
            return true;
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    public bool viewQuestionResult(GridView mGridView, string email, string examCode, string round)
    {
        try
        {
            datatable = new DataTable();
            datatable.Columns.Add("Number", typeof(int));
            datatable.Columns.Add("Content", typeof(string));
            datatable.Columns.Add("Answer", typeof(string));
            datatable.Columns.Add("YourAnswer", typeof(string));

            string[] quesCode = loadQuestionExam(examCode);
            string[] yourAnswer = getQuestionYourAnswer(email, round);
            int number = 0;
            for (int i = 0; i < quesCode.Length; i++)
            {
                string query = "SELECT [content],[answer] FROM Questions WHERE [quesCode]='" + quesCode[i] + "'";
                myConnection = new SqlConnection(connectionText);
                myCommand = new SqlCommand(query, myConnection);
                myConnection.Open();
                dataReader = myCommand.ExecuteReader();
                dataReader.Read();
                if (dataReader.HasRows)
                {
                    number++;
                    datatable.Rows.Add(number, dataReader["content"].ToString().Trim(), dataReader["answer"].ToString().Trim(), yourAnswer[i]);
                }
            }

            if (number > 0)
            {
                mGridView.DataSource = datatable;
                mGridView.DataBind();
                return true;
            }
            else
            {
                datatable.Clear();
                mGridView.DataSource = datatable;
                mGridView.DataBind();
            }
        }
        catch (Exception) { }
        finally { closeDB(); }
        return false;
    }

    private string[] getQuestionYourAnswer(string email, string round)
    {
        string[] yourAnswer = null;
        try
        {
            string query = "SELECT answertest FROM Tests WHERE [email]='" + email.Trim() + "' AND [round]='" + round.Trim() + "'";
            myConnection = new SqlConnection(connectionText);
            myCommand = new SqlCommand(query, myConnection);
            myConnection.Open();
            dataReader = myCommand.ExecuteReader();
            dataReader.Read();
            if (dataReader.HasRows)
            {
                yourAnswer = dataReader[0].ToString().Split(',');
            }
            for (int i = 0; i < yourAnswer.Length; i++)
            {
                if (yourAnswer[i].Trim().Equals("") || yourAnswer[i] == null)
                {
                    yourAnswer[i] = "<span style=\"color:#F00\">Not check</span>"; 
                }
            }
            return yourAnswer;
        }        
        catch (Exception) { }
        finally { closeDB(); }
        return null;
    }
}